Skip Headers

Oracle® Database Heterogeneous Connectivity Administrator's Guide 10g Release 2 (10.2) Part Number B14232-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 4 Using Heterogeneous Services Agents

This chapter explains how to use Heterogeneous Services (HS) agents. It contains the following sections:

- Setting Up Access to Non-Oracle Systems

- Setting Initialization Parameters

- Optimizing Data Transfers Using Bulk Fetch

- Registering Agents

- Oracle Database Server SQL Construct Processing

- Executing User-Defined Functions on a Non-Oracle Database

- Using Synonyms

- Copying Data from the Oracle Database Server to the Non-Oracle Database System

- Copying Data from the Non-Oracle Database System to the Oracle Database Server

- Using Heterogeneous Services Data Dictionary Views

- Using the Heterogeneous Services Dynamic Performance Views

## 4.1 Setting Up Access to Non-Oracle Systems

This section explains the generic steps to configure access to a non-Oracle system.

Note: The instructions for configuring your agent may differ slightly from the following instructions. Please see the installation and user's guide for your specific agent for more complete installation information.

The steps for setting up access to a non-Oracle system are:

Step 1: Configure Oracle Net Services to Access Heterogeneous Services Agents

Step 2: Create the Database Link to the Non-Oracle System

Step 3: Test the Connection

### 4.1.1 Step 1: Configure Oracle Net Services to Access Heterogeneous Services Agents

To initiate a connection to the non-Oracle system, the Oracle server starts an agent process through the Oracle Net listener. For the Oracle server to be able to connect to the agent, you must perform the following tasks:

1. Set up an Oracle Net service name for the agent that can be used by the Oracle server. The Oracle Net service name descriptor includes protocol-specific information needed to access the Oracle Net listener. The service name descriptor must include the `(HS=OK)` clause to ensure that the connection uses Oracle Heterogeneous Services. The description of this service name is defined in `tnsnames.ora`, the Oracle Names server, or in third-party name servers using the Oracle naming adapter.

   The following is a sample entry for a service name in the `tnsnames.ora` file:

       Sybase_sales= (DESCRIPTION=
                             (ADDRESS=(PROTOCOL=tcp)
                                      (HOST=sales)
                                      (PORT=1521)
                             )
                             (CONNECT_DATA = (SERVICE_NAME=SalesDB)
                             )
                             (HS = OK)
                     )

2. Set up the listener on the gateway to listen for incoming requests from the Oracle server and spawn Heterogeneous Services agents. Then, start the listener on the gateway system.

   The following is a sample entry for the listener in `listener.ora`:

    LISTENER =
           (ADDRESS_LIST =
              (ADDRESS= (PROTOCOL=tcp)
                        (HOST = sales)
                        (PORT = 1521)
              )
          )
        ...
        SID_LIST_LISTENER =
          (SID_LIST =
              (SID_DESC = (SID_NAME=SalesDB)
                          (ORACLE_HOME=/home/oracle/megabase/9.0.1)
                          (PROGRAM=tg4mb80)
                          (ENVS=LD_LIBRARY_PATH=non_oracle_system_lib_directory)
              )
          )

The value associated with the `PROGRAM` keyword defines the name of the agent executable. The full path of the directory which contains the DLLs that are loaded by the Heterogeneous Services agent is specified by `LD_LIBRARY_PATH`. You must use the same `SID_NAME` as used in the name of the initialization parameter file for the agent.

See Also: Oracle Database Net Services Administrator's Guide for more information about configuring the Oracle Net listener and a net service name for heterogeneous connectivity Section 4.2.1 about the initialization parameter file Section 5.3 for information about starting multithreaded agents

### 4.1.2 Step 2: Create the Database Link to the Non-Oracle System

To create a database link to the non-Oracle system, use the `CREATE DATABASE LINK` statement. The service name that is used in the `USING` clause of the `CREATE DATABASE LINK` statement is the Oracle Net service name.

Use quotes with the username and password to avoid differences in case-sensitive behavior between Oracle and non-Oracle databases. For example:

    CREATE DATABASE LINK sales
    CONNECT TO "sales1"
    IDENTIFIED BY "Sales1"
    USING 'Sybase_sales';

### 4.1.3 Step 3: Test the Connection

To test the connection to the non-Oracle system, use the database link in a SQL or PL/SQL statement. If the non-Oracle system is a SQL-based database, you can execute a `SELECT` statement from an existing table or view using the database link. For example:

    SELECT * FROM product@sales
       WHERE product_name like '%pencil%';

When you try to access the non-Oracle system for the first time, the Heterogeneous Services agent uploads information into the Heterogeneous Services data dictionary. The uploaded information includes:

Type of Data | Explanation
--- | ---
Capabilities of the non-Oracle system | The agent specifies whether it can perform a join or a `GROUP BY`.
SQL translation information | The agent specifies how to translate Oracle functions and operators into functions and operators of the non-Oracle system.
Data dictionary translations | To make the data dictionary information of the non-Oracle system available just as if it were an Oracle data dictionary, the agent specifies how to translate Oracle data dictionary tables into tables and views of the non-Oracle system.

Note: Most agents upload information into the Oracle data dictionary automatically the first time they are accessed. Some agent vendors may provide scripts, however, that you must run on the Oracle server.

See Also: Section 4.10 and Appendix C, "Data Dictionary Translation Support"

## 4.2 Setting Initialization Parameters

Configure the gateway using initialization parameters. This is done by creating an initialization file and setting the desired parameters in this file. See Section 2.5 for configuration information.

Heterogeneous Services initialization parameters are distinct from Oracle database server initialization parameters. Heterogeneous Services initialization parameters are set in the Heterogeneous Services initialization file and not in the Oracle database initialization parameter file (`init.ora` file). There is a Heterogeneous Services initialization file for each gateway instance.

### 4.2.1 Name and Location of Heterogeneous Services Initialization Parameter File

The name of the file is `init` `sid``.ora`, where `sid` is the Oracle system identifier used for the gateway.

In the case of Generic Connectivity, the Heterogeneous Services initialization file is located in the directory `$ORACLE_HOME/hs/admin`. In the case of Transparent Gateways, it is located in the directory `$ORACLE_HOME/``product_name``/admin` where `product_name` reflects the name of the product. For example, the Sybase gateway initialization file is located in the directory `$ORACLE_HOME/tg4sybs/admin`.

### 4.2.2 Syntax for Initialization Parameter Settings

The initialization file contains a list of initialization parameter settings each of which should be on a separate line. The syntax to set an initialization parameter is as follows:

    [SET] [PRIVATE] parameter = parameter_value

The `SET` and `PRIVATE` keywords are optional. If the `SET` keyword is present, the variable is also set in the environment. If the `PRIVATE` keyword is present, the parameter is not uploaded to the server. Oracle recommends that you do not use the `PRIVATE` keyword unless the initialization parameter value contains sensitive information (like a password) that should not be sent over the network from the gateway to the Oracle server.

In the initialization parameter syntax, all keywords (`SET`, `PRIVATE`, and `IFILE`) are case-insensitive. Initialization parameter names and values are case-sensitive. Most initialization parameter names are uppercase. String values for Heterogeneous Services parameters must be lowercase. Exceptions to this rule are explicitly noted.

Another initialization file can be included in an Heterogeneous Services initialization file by using the `IFILE` directive. The syntax for this is as follows:

    IFILE = path_name

### 4.2.3 Encrypting Initialization Parameters

Initialization parameters may contain sensitive information, such as user IDs or passwords. Initialization parameters are stored in plain text files and are insecure. An encryption feature has been added to Heterogeneous Services making it possible to encrypt parameter values. This is done through the `tg4pwd` utility. To use this feature requires setting the value of a parameter in the initialization file to an unquoted asterisk (*). For example:

    HD_FDS_CONNECT_INFO = *

With the value set to this security marker, all Heterogeneous Services agents know that the real value will be stored in a related, encrypted password file. The name of this file will be `init` `sid``.pwd`, where `sid` is the Oracle system identifier used for the gateway. This file is created by the `tg4pwd` utility in the current directory containing the initialization file. Running the utility prompts for the real value of the parameter, which the utility will encrypt and store in the password file. It should be noted that encrypted initialization parameters are implicitly treated as `PRIVATE` parameters and are not uploaded to the server.

#### 4.2.3.1 Using the tg4pwd Utility

The `tg4pwd` utility is used to encrypt initialization parameters that would normally be stored in the initialization parameter file in plain text. The utility works by reading the initialization parameter file in the current directory and looking for parameters having a security marker for the value. The security marker is an unquoted asterisk (*). This designates that the value of this parameter is to be stored in an encrypted form in a password file. The following is an example of an initialization parameter set to this value:

    HS_FDS_CONNECT_INFO = *

The initialization parameter file in the current directory is first edited to set the value of the parameter to this security marker. Then the utility is run, specifying the gateway SID on the command line, with an optional user ID to designate a different owner of the encrypted information. The utility reads the initialization parameter file and prompts you to enter the real values that are to be encrypted. The syntax of the command is:

    tg4pwd [sid] {userid}

Where `[``sid``]` is the SID of the gateway and `{``userid``}` is an optional user ID used to encrypt the contents. If no user ID is specified, then the current user's ID is used. Values are encrypted using this ID. In order to decrypt the values, the agent must be run as that user. The following example assumes a gateway SID of SYBASE:

    tg4pwd SYBASE
    ORACLE Gateway Password Utility
    Constructing password file for Gateway SID SYBASE
    Enter the value for HS_FDS_CONNECT_INFO
    sybasew

In the previous example, the initialization parameter file, `initSYBASE.ora`, is read. The parameter, `HS_FDS_CONNECT_INFO`, is identified as requiring encryption. Enter the value (for example, `sybasew`) and presses enter. If more parameters require encryption, they are prompted for in turn. The encrypted data is stored in the same directory as the initialization file.Any initialization parameters needing encryption should be encrypted before using the Gateway or Generic Connectivity.

### 4.2.4 Gateway Initialization Parameters

Gateway initialization parameters can be divided into two groups. One is a set of generic initialization parameters that are common to all gateways and the other is a set of initialization parameters that are specific to individual gateways. The following generic initialization parameters are the only initialization parameters discussed in this document:

`HS_CALL_NAME`
`HS_COMMIT_POINT_STRENGTH`
`HS_DB_DOMAIN`
`HS_DB_INTERNAL_NAME`
`HS_DB_NAME`
`HS_DESCRIBE_CACHE_HWM`
`HS_FDS_CONNECT_INFO`
`HS_FDS_DEFAULT_SCHEMA_NAME`
`HS_FDS_SHAREABLE_NAME`
`HS_FDS_TRACE_LEVEL`
`HS_LANGUAGE`
`HS_LONG_PIECE_TRANSFER_SIZE`
`HS_NLS_DATE_FORMAT`
`HS_NLS_DATE_LANGUAGE`
`HS_NLS_NCHAR`
`HS_NLS_NUMERIC_CHARACTERS`
`HS_NLS_TIMESTAMP_FORMAT`
`HS_NLS_TIMESTAMP_TZ_FORMAT`
`HS_OPEN_CURSORS`
`HS_ROWID_CACHE_SIZE`
`HS_RPC_FETCH_REBLOCKING`
`HS_RPC_FETCH_SIZE`
`HS_TIME_ZONE`

Do not use the `PRIVATE` keyword when setting any of these parameters. Using the `PRIVATE` keyword prevents the parameter from being uploaded to the server and can cause errors in SQL processing. None of these parameters needs to be set in the environment, so you do not need to use the `SET` keyword.

See Also: Appendix A, "Heterogeneous Services Initialization Parameters" for descriptions of the generic Heterogeneous Services initialization parameters Individual gateway documentation for the list of initialization parameters specific to a gateway

## 4.3 Optimizing Data Transfers Using Bulk Fetch

When an application fetches data from a non-Oracle system using Heterogeneous Services, data is transferred:

1. From the non-Oracle system to the agent process.

2. From the agent process to the Oracle database server.

3. From the Oracle database server to the application.

Oracle optimizes all three data transfers, as illustrated in Figure 4-1.

Figure 4-1 Optimizing Data Transfers

Description of the illustration heter009.gif

This section contains the following topics:

- Using OCI, an Oracle Precompiler, or Another Tool for Array Fetches

- Controlling the Array Fetch Between the Oracle Database Server and the Agent

- Controlling the Array Fetch Between Agent and Non-Oracle Server

- Controlling the Reblocking of Array Fetches

### 4.3.1 Using OCI, an Oracle Precompiler, or Another Tool for Array Fetches

You can optimize data transfers between your application and the Oracle server by using array fetches. See your application development tool documentation for information about array fetching and how to specify the amount of data to be sent and each network round-trip.

### 4.3.2 Controlling the Array Fetch Between the Oracle Database Server and the Agent

When Oracle retrieves data from a non-Oracle system, the Heterogeneous Services initialization parameter, `HS_RPC_FETCH_SIZE`, defines the number of bytes sent for each fetch between the agent and the Oracle server. The agent fetches data from the non-Oracle system until one of the following occurs:

- It has accumulated the specified number of bytes to send back to the Oracle database server.

- The last row of the result set is fetched from the non-Oracle system.

### 4.3.3 Controlling the Array Fetch Between Agent and Non-Oracle Server

The initialization parameter, `HS_FDS_FETCH_ROWS`, determines the number of rows to be retrieved from a non-Oracle system. Note that the array fetch must be supported by the agent. See your agent-specific documentation to ensure that your agent supports array fetching.

### 4.3.4 Controlling the Reblocking of Array Fetches

By default, an agent fetches data from the non-Oracle system until it has enough data retrieved to send back to the server. It continues until the number of bytes fetched from the non-Oracle system is equal to or higher than the value of `HS_RPC_FETCH_SIZE` initialization parameter. In other words, the agent reblocks the data between the agent and the Oracle database server in sizes defined by the value of the `HS_RPC_FETCH_SIZE` initialization parameter.

When the non-Oracle system supports array fetches, you can immediately send the data fetched from the non-Oracle system by the array fetch to the Oracle database server without waiting until the exact value of the `HS_RPC_FETCH_SIZE` initialization parameter is reached. That is, you can stream the data from the non-Oracle system to the Oracle database server and disable reblocking by setting the value of the `HS_RPC_FETCH_REBLOCKING` initialization parameter to `OFF`.

For example, assume that you set `HS_RPC_FETCH_SIZE` to 64 kilobytes (KB) and `HS_FDS_FETCH_ROWS` to 100 rows. Also assume that each row is approximately 600 bytes in size, so that the 100 rows are approximately 60 KB. When the `HS_RPC_FETCH_REBLOCKING` initialization parameter is set to `ON`, the agent starts fetching 100 rows from the non-Oracle system.

Because there is only 60 KB of data in the agent, the agent does not send the data back to the Oracle database server. Instead, the agent fetches the next 100 rows from the non-Oracle system. Because there is now 120 KB of data in the agent, the first 64 KB can be sent back to the Oracle database server.

Now there is 56 KB of data left in the agent. The agent fetches another 100 rows from the non-Oracle system before sending the next 64 KB of data to the Oracle database server. By setting the `HS_RPC_FETCH_REBLOCKING` initialization parameter to `OFF`, the first 100 rows are immediately sent back to the Oracle database server.

## 4.4 Registering Agents

Registration is an operation through which Oracle stores information about an agent in the data dictionary. Agents do not have to be registered. If an agent is not registered, Oracle stores information about the agent in memory instead of in the data dictionary. When a session involving an agent terminates, this information ceases to be available.

Self-registration is an operation in which a database administrator sets an initialization parameter that lets the agent automatically upload information into the data dictionary. Self-registration occurs when the `HS_AUTOREGISTER` initialization parameter is set to `TRUE` (default).

Note: `HS_AUTOREGISTER` is an Oracle initialization parameter that you set in the `init.ora` file; it is not a Heterogeneous Services initialization parameter that is set in the gateway initialization file.

This section contains the following topics:

- Enabling Agent Self-Registration

- Disabling Agent Self-Registration

### 4.4.1 Enabling Agent Self-Registration

To ensure correct operation over heterogeneous database links, agent self-registration automates updates to Heterogeneous Services configuration data that describe agents on remote hosts. Agent self-registration is the default behavior. If you do not want to use the agent self-registration feature, set the `HS_AUTOREGISTER` initialization parameter to `FALSE`.

Both the server and the agent rely on three types of information to configure and control operation of the Heterogeneous Services connection. These three sets of information are collectively called HS configuration data:

Heterogeneous Services Configuration Data | Description
--- | ---
Heterogeneous Services initialization parameters | Provide control over various connection-specific details of operation.
Capability definitions | Identify details like SQL language features supported by the non-Oracle data source.
Data dictionary translations | Map references to Oracle data dictionary tables and views into equivalents specific to the non-Oracle data source.

See Also: Section 4.4.1.3, "Specifying HS_AUTOREGISTER"

#### 4.4.1.1 Using Agent Self-Registration to Avoid Configuration Mismatches

HS configuration data is stored in the data dictionary of the Oracle database server. Because the agent may be remote and may therefore be administered separately, several circumstances can lead to configuration mismatches between servers and agents. For example:

- An agent can be newly installed on a separate machine so that the server has no Heterogeneous Services data dictionary content to represent the agent's Heterogeneous Services configuration data.

- A server can be newly installed and lack the necessary Heterogeneous Services configuration data for existing agents and non-Oracle data stores.

- A non-Oracle instance can be upgraded from an older version to a newer version, requiring modification of the Heterogeneous Services configuration data.

- A Heterogeneous Services agent at a remote site can be upgraded to a new version or patched, requiring modification of the Heterogeneous Services configuration data.

- A database administrator (DBA) at the non-Oracle site can change the agent setup, possibly for tuning or testing purposes, in a manner which affects Heterogeneous Services configuration data.

Agent self-registration permits successful operation of Heterogeneous Services in all these scenarios. Specifically, agent self-registration enhances interoperability between any Oracle database server and any Heterogeneous Services agent (if each is Version 8.0.3 or higher). The basic mechanism for this functionality is the ability to upload Heterogeneous Services configuration data from agents to servers.

Self-registration provides automatic updating of Heterogeneous Services configuration data residing in the Oracle database server data dictionary. This update ensures that the agent self-registration uploads need to be done only once, on the initial use of a previously unregistered agent. Instance information is uploaded on each connection, not stored in the server data dictionary.

#### 4.4.1.2 Understanding Agent Self-Registration

The Heterogeneous Services agent self-registration feature can perform the following tasks:

- Identify the agent and the non-Oracle data store to the Oracle database server

- Permit agents to define Heterogeneous Services initialization parameters for use both by the agent and connected Oracle servers

- Upload capability definitions and data dictionary translations, if available, from a Heterogeneous Services agent during connection initialization

  Note: The upload of class information occurs only when the class is undefined in the server data dictionary. Similarly, instance information is uploaded only if the instance is undefined in the server data dictionary.

The information required for agent self-registration is accessed in the server data dictionary by using these agent-supplied names:

- `FDS_CLASS`

- `FDS_CLASS_VERSION`

  See Also: Section 4.10, "Using Heterogeneous Services Data Dictionary Views" to learn how to use the Heterogeneous Services data dictionary views

##### 4.4.1.2.1 FDS_CLASS and FDS_CLASS_VERSION

`FDS_CLASS` and `FDS_CLASS_VERSION` are defined by Oracle or by third-party vendors for each individual Heterogeneous Services agent and version. Oracle Heterogeneous Services concatenates these names to form `FDS_CLASS_NAME`, which is used as a primary key to access class information in the server data dictionary.

`FDS_CLASS` should specify the type of non-Oracle data store to be accessed and `FDS_CLASS_VERSION` should specify a version number for both the non-Oracle data store and the agent that connects to it. Note that when any component of an agent changes, `FDS_CLASS_VERSION` must also change to uniquely identify the new release.

Note: This information is uploaded when you initialize each connection.

##### 4.4.1.2.2 FDS_INST_NAME

Instance-Specific Information can be stored in the server data dictionary. The instance name, `FDS_INST_NAME`, is configured by the database administrator (DBA) who administers the agent. How the DBA performs this configuration depends on the specific agent in use.

The Oracle database server uses `FDS_INST_NAME` to look up instance-specific configuration information in its data dictionary. Oracle uses the value as a primary key for columns of the same name in these views:

- `FDS_INST_INIT`

- `FDS_INST_CAPS`

- `FDS_INST_DD`

Server data dictionary accesses that use `FDS_INST_NAME` also use `FDS_CLASS_NAME` to uniquely identify configuration information rows. For example, if you port a database from class Sybase816 to class Sybase817, both databases can simultaneously operate with instance name `SCOTT` and use separate sets of configuration information.

Unlike class information, instance information is not automatically self-registered in the server data dictionary:

- If available, instance information is always uploaded by the agent. However, it is never stored in the server data dictionary. Instead, the information is kept in memory and it is only valid for that connection.

- If the server data dictionary contains instance information, it represents the DBA's defined setup details which correspond to the instance configuration. Data dictionary defined instance information takes precedence over class information. However, uploaded instance information takes precedence over data dictionary defined instance information.

#### 4.4.1.3 Specifying HS_AUTOREGISTER

The `HS_AUTOREGISTER` Oracle database server initialization parameter enables or disables automatic self-registration of Heterogeneous Services agents. Note that this parameter is specified in the Oracle initialization parameter file, not the agent initialization file. For example, you can set the parameter as follows:

    HS_AUTOREGISTER = TRUE

When set to `TRUE`, the agent uploads information describing a previously unknown agent class or a new agent version into the server's data dictionary.

Oracle recommends that you use the default value for this parameter (`TRUE`), which ensures that the server's data dictionary content always correctly represents definitions of class capabilities and data dictionary translations as used in Heterogeneous Services connections.

See Also: Oracle Database Reference for a description of this parameter

### 4.4.2 Disabling Agent Self-Registration

To disable agent self-registration, set the `HS_AUTOREGISTER` initialization parameter as follows:

    HS_AUTOREGISTER = FALSE

Disabling agent self-registration means that agent information is not stored in the data dictionary. Consequently, the Heterogeneous Services data dictionary views are not useful sources of information. Nevertheless, the Oracle server still requires information about the class and instance of each agent. If agent self-registration is disabled, the server stores this information in local memory.

## 4.5 Oracle Database Server SQL Construct Processing

Heterogeneous Services and the gateway rewrite SQL statements when the statements need to be translated or postprocessed.

For the following examples, assume the `INITCAP` function is not supported in the non-Oracle database. Consider a program that requests the following from the non-Oracle database. For example:

    SELECT "COLUMN_A" FROM "test"@remote_db
        WHERE "COLUMN_A" = INITCAP("COLUMN_B");

The non-Oracle database does not recognize the `INITCAP` function, so the Oracle database server fetches the data from the table `test` in the remote database and filters the results locally. The gateway rewrites the `SELECT` statement as follows:

    SELECT "COLUMN_A", "COLUMN_B" FROM "test"@remote_db;

The results of the query are sent from the gateway to Oracle and are filtered by the Oracle database server.

If a string literal or bind variable is supplied in place of `"COLUMN_B"` as shown in the previous example, the Heterogeneous Services component of the Oracle server would apply the `INITCAP` function before sending the SQL command to the gateway. For example, if the following SQL command is issued:

    SELECT "COLUMN_A" FROM "test"@remote_db WHERE "COLUMN_A" = INITCAP('jones');

The following SQL command would be sent to the gateway:

    SELECT "COLUMN_A" FROM "test"@remote_db WHERE "COLUMN_A" = 'Jones';

Consider the following `UPDATE` request:

    UPDATE "test"@remote_db SET "COLUMN_A" = 'new_value'
        WHERE "COLUMN_A" = INITCAP("COLUMN_B");

In this case, the Oracle database server and the gateway cannot compensate for the lack of support at the non-Oracle side, so an error is issued.

If a string literal or bind variable is supplied in place of `"COLUMN_B"` as shown in the preceding example, the Heterogeneous Services component of the Oracle server would apply the `INITCAP` function before sending the SQL command to the gateway. For example, if the following SQL command is issued:

    UPDATE "test"@remote_db SET "COLUMN_A" = 'new_value'
        WHERE "COLUMN_A" = INITCAP('jones');

The following SQL command would be sent to the gateway:

    UPDATE  "test"@remote_db SET "COLUMN_A" = 'new_value'
        WHERE "COLUMN_A" = 'Jones';

In previous releases, the preceding `UPDATE` statement would have raised an error due to the lack of `INITCAP` function support in the non-Oracle database.

### 4.5.1 Data Type Checking Support for a Remote-Mapped Statement

The Oracle database has always performed data type checking and data type coercion in a homogeneous environment. For example, `SELECT * FROM EMP WHERE EMPNO='7934'` would return the same result as `SELECT * FROM EMPNO WHERE EMPNO=7934`. There is also full data type checking support for remote-mapped statements in a heterogeneous environment. In general, the operands in SQL statements whether its a column, literal, or bind variable would be processed internally for data type checking. Consider the following examples:

    SELECT * FROM EMP@LINK WHERE NUMBER_COLUMN='123'
    SELECT * FROM EMP@LINK WHERE NUMBER_COLUMN=CHAR_COLUMN;
    SELECT * FROM EMP@LINK WHERE NUMBER_COLUMN=CHAR_BIND_VARIABLE;

Most non-Oracle databases do not support data type coercion, and the previous statements fail if they are sent to a non-Oracle database as is. The Heterogeneous Services component for the Oracle database performs data type checking and the necessary data type coercion before sending an acceptable statement to a non-Oracle database.

Data type checking provides consistent behavior on post-processed or remote-mapped statements. Consider the following two statements:

    SELECT * FROM EMP@LINK WHERE TO_CHAR(EMPNO)='7933' + '1';

And:

    SELECT * FROM EMP@LINK WHERE EMPNO='7933' + '1';

Both of the previous statements provide the same result and coercion regardless if the `TO_CHAR` function is supported in the non-Oracle database or not. Now, consider the following statement:

    SELECT * FROM EMP@LINK WHERE EMPNO='123abc' + '1';

As data type checking is enforced, the coercion attempt within Oracle generates an error and returns it without sending any statements to a non-Oracle database.

In summary, there is consistent data type checking and coercion behavior regardless of post-processed or remote-mapped statements.

## 4.6 Executing User-Defined Functions on a Non-Oracle Database

You can execute user-defined functions in a remote non-Oracle database. For example:

    SELECT getdeptforemp@Remote_DB(7782) FROM dual;

In this example, a `SELECT` statement was issued that executes a user-defined function in the remote database that returns department information for employee 7782.

When the remote function resides in an Oracle database, the Oracle database automatically ensures that the remote function does not update any database state (such as updating rows in a database or updating the PL/SQL package state). The gateway cannot verify this when the remote function resides in a non-Oracle database. Therefore, you are responsible for ensuring that the user-defined functions do not update the state in any database. Ensuring no updates to the database is required to guarantee read consistency.

As a security measure, you must specify the functions that you want to execute remotely and their owners in the `HS_CALL_NAME` parameter in the gateway-specific initialization parameter file. For example:

    HS_CALL_NAME = "owner1.A1, owner2.A2 "

`owner1` and `owner2` are the remote function owner names. `A1` and `A2` are the remote function names. You do not need to specify the remote function owner in the SQL statement. By default, the remote function needs to reside in the schema that the Transparent Gateway connects to. If this is not the case, then you must specify the owner of the remote function in the SQL statement.

Some other examples of executing user-defined remote functions are as follows:

- A remote function in a subquery

  The function uses the `employee_id` column data to retrieve the `department_id` from the `EMPLOYEES` table in the remote database. The outer query then determines all department numbers in the remote database that match the returned list.

      SELECT * FROM departments@remotedb
        WHERE department_id IN
           (SELECT
            getdeptforemp@remotedb (employee_id)
            FROM employees@remotedb);

- Applying a local function to the result of a user-defined remote function

  This query returns the maximum salary of all employees on the remote database.

      SELECT max (getsalforemp@remotedb (employee_id))
         FROM employees@remotedb;

- A DML statement

  The statement uses the output from a user-defined query in the remote database to update the salary column with new salary information.

      UPDDATE employee_history
         SET salary = emp_changed_salary@remote_db;

In these examples, the Oracle database passes the function name and owner to the Transparent Gateway. The user-defined function is executed on the remote database.

See Also: Section A.1, "HS_CALL_NAME"

## 4.7 Using Synonyms

You can provide complete data location transparency and network transparency by using the synonym feature of the Oracle database server. When a synonym is defined, you do not have to know the underlying table or network protocol. A synonym can be public, which means that all Oracle users can refer to the synonym. A synonym can also be defined as private, which means every Oracle user must have a synonym defined to access the non-Oracle table.

The following statement creates a system-wide synonym for the `emp` table in the schema of user `ORACLE` in the Sybase database:

    CREATE PUBLIC SYNONYM emp FOR "ORACLE"."EMP"@SYBS;

See Also: Oracle Database Administrator's Guide for information about synonyms

### 4.7.1 Example of a Distributed Query

Note: Modify these examples for your environment. Do not try to execute them as they are written.

The following statement joins data between the Oracle database server, an IBM DB2 database, and a Sybase database:

    SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P."HOURS")
           FROM ORDERS@DB2 O, EMP@ORACLE9 E, "PROJECTS"@SYBS P
           WHERE O.PROJNO = P."PROJNO"
              AND P."EMPNO" = E.EMPNO
           GROUP BY O.CUSTNAME, P."PROJNO", E.ENAME;

Through a combination of views and synonyms, using the following SQL statements, the process of distributed queries is transparent:

    CREATE SYNONYM ORDERS FOR ORDERS@DB2;
    CREATE SYNONYM PROJECTS FOR "PROJECTS"@SYBS;
    CREATE VIEW DETAILS (CUSTNAME,PROJNO,ENAME,SPEND)
          AS
          SELECT O.CUSTNAME, P."PROJNO", E.ENAME, SUM(E.RATE*P."HOURS")
          SPEND
          FROM ORDERS O, EMP E, PROJECTS P
          WHERE O.PROJNO = P."PROJNO"
          AND P."EMPNO" = E.EMPNO
          GROUP BY O.CUSTNAME, P."PROJNO", E.ENAME;

Use the following SQL statement to retrieve information from the data stores in one statement:

    SELECT * FROM DETAILS;

The statement retrieves the following table:

    CUSTNAME         PROJNO           ENAME          SPEND
    --------         ------           -----          -----
    ABC Co.             1             Jones            400
    ABC Co.             1             Smith            180
    XYZ Inc.            2             Jones            400
    XYZ Inc.            2             Smith            180

## 4.8 Copying Data from the Oracle Database Server to the Non-Oracle Database System

Heterogeneous Services supports callback links. This enables SQL statements like the following to be executed:

    INSERT INTO table_name@dblink SELECT column_list FROM table_name;

Even though Heterogeneous Services supports the callback functionality, not all gateways have implemented it. If the gateway that you are using has not implemented this functionality, the preceding `INSERT` statement returns the following error message:

    ORA-02025: All tables in the SQL statement must be at the remote database

See Also: Your gateway documentation for information about support for callback links

For gateways that do not support callback links, you can use the SQL*Plus `COPY` command. The syntax is as follows:

    COPY FROM username/password@db_name -
       INSERT destination_table -
       USING query;

The following example selects all rows from the local Oracle `emp` table, inserts them into the `emp` table on the non-Oracle database, and commits the transaction:

    COPY FROM SCOTT/TIGER@inst1 -
      INSERT EMP@remote_db -
      USING SELECT * FROM EMP;

    The COPY command supports the APPEND, CREATE, INSERT, and REPLACE options. However, INSERT is the only option supported when copying to non-Oracle databases. The SQL*Plus COPY command does not support copying to tables with lowercase table names. Use the following PL/SQL syntax with lowercase table names:

    DECLARE
        v1 oracle_table.column1%TYPE;
        v2 oracle_table.column2%TYPE;
        v3 oracle_table.column3%TYPE;
        .
        .
        .
        CURSOR cursor_name IS SELECT * FROM oracle_table;
    BEGIN
        OPEN cursor_name;
        LOOP
           FETCH cursor_name INTO v1, v2, v3, ... ;
           EXIT WHEN cursor_name%NOTFOUND;
           INSERT INTO destination_table VALUES (v1, v2, v3, ...);
        END LOOP;

        CLOSE cursor_name;
    END;

See Also: SQL*Plus User's Guide and Reference for more information about the `COPY` command

## 4.9 Copying Data from the Non-Oracle Database System to the Oracle Database Server

The `CREATE TABLE` statement lets you copy data from a non-Oracle database to the Oracle database. To create a table on the local database and insert rows from the non-Oracle table, use the following syntax:

    CREATE TABLE table_name AS query;

The following example creates the table `emp` in the local Oracle database and inserts the rows from the `EMP` table of the non-Oracle database:

    CREATE TABLE table1 AS SELECT * FROM "EMP"@remote_db;

    Alternatively, you can use the SQL*Plus COPY command to copy data from the non-Oracle database to the Oracle database server.

See Also: SQL*Plus User's Guide and Reference for more information about the `COPY` command

## 4.10 Using Heterogeneous Services Data Dictionary Views

You can use the Heterogeneous Services data dictionary views to access information about Heterogeneous Services. This section addresses the following topics:

- Understanding the Types of Views

- Understanding the Sources of Data Dictionary Information

- Using the General Views

- Using the Transaction Service Views

- Using the SQL Service Views

### 4.10.1 Understanding the Types of Views

The Heterogeneous Services data dictionary views, whose names all begin with the `HS_` prefix, can be divided into the following categories:

- General views

- Views used for the transaction service

- Views used for the SQL service

Most of the data dictionary views are defined for both classes and instances. For most types of data there is a `*_CLASS` view and a `*_INST` view. See Table 4-1 for additional details.

Table 4-1 Data Dictionary Views for Heterogeneous Services

View | Type | Identifies
--- | --- | ---
`HS_BASE_CAPS` | SQL service | All capabilities supported by Heterogeneous Services
`HS_BASE_DD` | SQL service | All data dictionary translation table names supported by Heterogeneous Services
`HS_CLASS_CAPS` | Transaction service, SQL service | Capabilities for each class
`HS_CLASS_DD` | SQL service | Data dictionary translations for each class
`HS_CLASS_INIT` | General | Initialization parameters for each class
`HS_FDS_CLASS` | General | Classes accessible from the Oracle server
`HS_FDS_INST` | General | Instances accessible from the Oracle server
`HS_INST_CAPS` | Transaction service, SQL service | Capabilities for each instance (if set up by the DBA)
`HS_INST_DD` | SQL service | Data dictionary translations for each class (if set up by the DBA)
`HS_INST_INIT` | General | Initialization parameters for each instance (if set up by the DBA)

Like all Oracle data dictionary tables, these views are read-only. Do not change the content of any of the underlying tables.

### 4.10.2 Understanding the Sources of Data Dictionary Information

The values used for data dictionary content in any particular connection on a Heterogeneous Services database link can come from any of the following sources, in order of precedence:

- Instance information uploaded by the connected Heterogeneous Services agent at the start of the session. This information overrides corresponding content in the Oracle data dictionary, but is never stored into the Oracle data dictionary.

- Instance information stored in the Oracle data dictionary. This data overrides any corresponding content for the connected class.

- Class information stored in the Oracle data dictionary.

If the Oracle database server runs with the `HS_AUTOREGISTER` server initialization parameter set to `FALSE`, then information is not stored automatically in the Oracle data dictionary. The equivalent data is uploaded by the Heterogeneous Services agent on a connection-specific basis each time a connection is made, with any instance-specific information taking precedence over class information.

Note: It is not possible to determine positively what capabilities and what data dictionary translations are in use for a given session due to the possibility that an agent can upload instance information.

You can determine the values of Heterogeneous Services initialization parameters by querying the `VALUE` column of the `V$HS_PARAMETER` view. Note that the `VALUE` column of `V$HS_PARAMETER` truncates the actual initialization parameter value from a maximum of 255 characters to a maximum of 64 characters. It truncates the parameter name from a maximum of 64 characters to a maximum of 30 characters.

### 4.10.3 Using the General Views

The views that are common for all services are as follows:

View | Contains
--- | ---
`HS_FDS_CLASS` | Names of the classes that are uploaded into the Oracle data dictionary
`HS_FDS_INST` | Names of the instances that are uploaded into the Oracle data dictionary
`HS_CLASS_INIT` | Information about the Heterogeneous Services initialization parameters

For example, you can access multiple Sybase gateways from an Oracle database server. After accessing the gateways for the first time, the information uploaded into the Oracle database server could appear as follows:

    SQL> SELECT * FROM HS_FDS_CLASS;

    FDS_CLASS_NAME        FDS_CLASS_COMMENTS             FDS_CLASS_ID
    --------------------- ------------------------------ ------------
    Sybase816             Uses Sybase driver, R1.1                 1
    Sybase817             Uses Sybase driver, R1.2                21

    Two classes are uploaded: a class that accesses Sybase816 and a class that accesses Sybase817. The data dictionary in the Oracle database server now contains capability information, SQL translations, and data dictionary translations for both Sybase816 and Sybase817.

    The Oracle database server data dictionary also contains instance information in the HS_FDS_INST view for each non-Oracle system instance that is accessed.

### 4.10.4 Using the Transaction Service Views

When a non-Oracle system is involved in a distributed transaction, the transaction capabilities of the non-Oracle system and the agent control whether it can participate in distributed transactions. Transaction capabilities are stored in the `HS_CLASS_CAPS` tables.

The ability of the non-Oracle system and agent to support two-phase commit protocols is specified by the 2PC type capability, which can specify one of the types shown in the following table.

Type | Capability
--- | ---
Read-Only (RO) | The non-Oracle system can be queried only with SQL `SELECT` statements. Procedure calls are not allowed because procedure calls are assumed to write data.
Single-Site (SS) | The non-Oracle system can handle remote transactions but not distributed transactions. That is, it cannot participate in the two-phase commit protocol.
Commit Confirm (CC) | The non-Oracle system can participate in distributed transactions. It can participate in the server's two-phase commit protocol but only as the commit point site. That is, it cannot prepare data, but it can remember the outcome of a particular transaction if asked by the global coordinator.
Two-Phase Commit (2PC) | The non-Oracle system can participate in distributed transactions. It can participate in the server's two-phase commit protocol, as a regular two-phase commit node, but not as a commit point site. That is, it can prepare data, but it cannot remember the outcome of a particular transaction if asked to by the global coordinator.
Two-Phase Commit Confirm (2PCC) | The non-Oracle system can participate in distributed transactions. It can participate in the server's two-phase commit protocol as a regular two-phase commit node or as the commit point site. That is, it can prepare data and it can remember the outcome of a particular transaction if asked by the global coordinator.

The transaction model supported by the driver and non-Oracle system can be queried from the `HS_CLASS_CAPS` Heterogeneous Services data dictionary view.

The following example shows one of the capabilities is of the 2PC type:

    SELECT cap_description, translation
    FROM   hs_class_caps
    WHERE  cap_description LIKE '2PC%'
    AND    fds_class_name LIKE 'SYBASE%';

    CAP_DESCRIPTION                          TRANSLATION
    ---------------------------------------- -----------
    2PC type (RO-SS-CC-PREP/2P-2PCC)                  CC

    When the non-Oracle system and agent support distributed transactions, the non-Oracle system is treated like any other Oracle server. When a failure occurs during the two-phase commit protocol, the transaction is recovered automatically. If the failure persists, the in-doubt transaction may need to be manually overridden by the database administrator.

### 4.10.5 Using the SQL Service Views

Data dictionary views that are specific for the SQL service contain information about:

- SQL capabilities and SQL translations of the non-Oracle data source

- Data dictionary translations to map Oracle data dictionary views to the data dictionary of the non-Oracle system

  Note: This section describes only a portion of the SQL Service-related capabilities. Because you should never need to alter these settings for administrative purposes, these capabilities are not discussed here.

#### 4.10.5.1 Using Views for Capabilities and Translations

The `HS_*_CAPS` data dictionary tables contain information about the SQL capabilities of the non-Oracle data source and required SQL translations. These views specify whether the non-Oracle data store or the Oracle database server implements certain SQL language features. If a capability is turned off, then Oracle does not send any SQL statements to the non-Oracle data source that require this particular capability, but it still performs postprocessing.

#### 4.10.5.2 Using Views for Data Dictionary Translations

In order to make the non-Oracle system appear similar to an Oracle database server, Heterogeneous Services connections map a limited set of Oracle data dictionary views onto the non-Oracle system's data dictionary. This mapping permits applications to issue queries as if these views belonged to an Oracle data dictionary. Data dictionary translations make this access possible. These translations are stored in Heterogeneous Services views whose names have the `_DD` suffix.

For example, the following `SELECT` statement transforms into a Sybase query that retrieves information about `emp` tables from the Sybase data dictionary table:

SELECT * FROM USER_TABLES@remote_db WHERE UPPER(TABLE_NAME)='EMP';

Data dictionary tables can be mimicked instead of translated. If a data dictionary translation is not possible because the non-Oracle data source does not have the required information in its data dictionary, then Heterogeneous Services causes it to appear as if the data dictionary table is available, but the table contains no information.

To retrieve information about which Oracle data dictionary views or tables are translated or mimicked for the non-Oracle system, connect as user `SYS` and issue the following query on the `HS_CLASS_DD` view:

    SELECT DD_TABLE_NAME, TRANSLATION_TYPE
    FROM   HS_CLASS_DD
    WHERE  FDS_CLASS_NAME LIKE 'SYBASE%';

    DD_TABLE_NAME                  T
    -----------------------------  -
    ALL_ARGUMENTS                  M
    ALL_CATALOG                    T
    ALL_CLUSTERS                   T
    ALL_CLUSTER_HASH_EXPRESSIONS   M
    ALL_COLL_TYPES                 M
    ALL_COL_COMMENTS               T
    ALL_COL_PRIVS                  M
    ALL_COL_PRIVS_MADE             M
    ALL_COL_PRIVS_RECD             M
    ...

The `T` translation type specifies that a translation exists. When the translation type is `M`, the data dictionary table is mimicked.

See Also: Appendix C, "Data Dictionary Translation Support" for a list of data dictionary views that are supported through Heterogeneous Services mapping

## 4.11 Using the Heterogeneous Services Dynamic Performance Views

The Oracle database server stores information about agents, sessions, and parameters. You can use the dynamic performance views to access this information. This section contains the following topics:

- Determining Which Agents Are Running on a Host: V$HS_AGENT View

- Determining the Open Heterogeneous Services Sessions: V$HS_SESSION View

- Determining the Heterogeneous Services Parameters: V$HS_PARAMETER View

### 4.11.1 Determining Which Agents Are Running on a Host: V$HS_AGENT View

The `V$HS_AGENT` view identifies the set of Heterogeneous Services agents currently operating on a specified host. Table 4-2 shows the most relevant columns. For a description of all the columns in the view, see Oracle Database Reference.

Table 4-2 Important Columns in the V$HS_AGENT View

Column | Description
--- | ---
`AGENT_ID` | Oracle Net session identifier used for connections to agent (`listener.ora SID`)
`MACHINE` | Operating system machine name
`PROGRAM` | Program name of agent
`AGENT_TYPE` | Type of agent
`FDS_CLASS_ID` | The ID of the foreign data store class
`FDS_INST_ID` | The instance name of the foreign data store

### 4.11.2 Determining the Open Heterogeneous Services Sessions: V$HS_SESSION View

The `V$HS_SESSION` view shows the sessions for each agent and specifies the database link that is used. Table 4-3 shows the most relevant columns. For a description of all the columns in the view, see Oracle Database Reference.

Table 4-3 Important Columns in the V$HS_SESSION View

Column | Description
--- | ---
`HS_SESSION_ID` | Unique Heterogeneous Services session identifier
`AGENT_ID` | Oracle Net session identifier used for connections to agent (`listener.ora SID`)
`DB_LINK` | Server database link name used to access the agent `NULL` means that no database link is used (for example, when using external procedures)
`DB_LINK_OWNER` | Owner of the database link in `DB_LINK`

### 4.11.3 Determining the Heterogeneous Services Parameters: V$HS_PARAMETER View

The `V$HS_PARAMETER` view lists the Heterogeneous Services parameters and their values that are registered in the Oracle database server. Table 4-4 shows the most relevant columns. For a description of all the columns in the view, see Oracle Database Reference.

Table 4-4 Important Columns in the V$HS_SESSION View

Column | Description
--- | ---
`HS_SESSION_ID` | Unique Heterogeneous Services session identifier
`PARAMETER` | The name of the Heterogeneous Services parameter
`VALUE` | The value of the Heterogeneous Services parameter

Information about the database link that was used for establishing the distributed connection, the startup time, and the set of initialization parameters used for the session is also available. All of the runtime information is derived from dynamically updated tables.